import pandas as pd
import json
import re

def clean_data(data):
    if isinstance(data, str):
        # 只清理空格和换行符，避免处理成 JSON 后转义
         return re.sub(r'[\r\n\t ]+', '', data) # 去除换行符和空格
    return data

# 你可以在读取 Excel 后调用这个函数来处理你的数据
def ReadExcel(file_path):
    df = pd.read_excel(file_path, header=None)
    print("原始数据:")
    print(df.head()) 

    matching_rows = df[df.iloc[:, 0].str.contains('项目名称', na=False)]
    if not matching_rows.empty:
        row_index = matching_rows.index[0]
        project_name_value = df.iloc[row_index, 1]
    else:
        project_name_value = "未找到包含‘项目名称’的行"

    env_row = df[df.iloc[:, 0].str.contains('测试环境', na=False)]
    if not env_row.empty:
        env_value = env_row.iloc[0, 1]
    else:
        env_value = "未找到包含‘测试环境’的行"

    df = df.drop([0, 1], errors='ignore').reset_index(drop=True)

    column_names = df.iloc[0].tolist()
    df = df.drop(0).reset_index(drop=True)
    
    def make_unique(col_names):
        seen = set()
        result = []
        for name in col_names:
            new_name = name
            while new_name in seen:
                new_name = f"{name}_dup"
            seen.add(new_name)
            result.append(new_name)
        return result
    
    df.columns = make_unique(column_names)

    df['请求体'] = df['请求体'].apply(clean_data)
    df['预计结果'] = df['预计结果'].apply(clean_data)

    # 如果"依赖数据"列是空的，将其设为 None
    df['依赖数据'] = df['依赖数据'].apply(lambda x: None if pd.isna(x) or x == "" else x)
    df['依赖数据'] = df['依赖数据'].apply(clean_data)

    # 将每一行转成字典
    data_dict_list = df.to_dict(orient='records')

    return json.dumps({
        "data_dict_list": data_dict_list,
        "project_name_value": project_name_value,
        "env_value": env_value
    }, ensure_ascii=False)
